﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using saigonbook.Dal;
using System.Text.RegularExpressions;


namespace saigonbook.Biz
{
    public class Report
    {
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        public bool IsNumber(string pText)
        {
            Regex regex = new Regex(@"^[-+]?[0-9]*\.?[0-9]+$");
            return regex.IsMatch(pText);
        }

        public void ExportToExcel_nhap(DataGridView danhsachmuahang, TextBox tongsoluong, TextBox tongtien ,ComboBox thang, ComboBox nam)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet1 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet2 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet3 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet1.Name = "Thống kê tình hình mua hàng";
            xlWorkSheet1.Cells[1, 1] = "CỬA HÀNG SÁCH SAIGONBOOK";
            xlWorkSheet1.get_Range("A1", "D1").Merge(true);
            xlWorkSheet1.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet1.get_Range("A1", "D1").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A1", "D1").Font.Color = 32768;

            xlWorkSheet1.Cells[2, 1] = "Địa chỉ: 273 An Dương Vương, Quận 5";
            xlWorkSheet1.get_Range("A2", "D2").Merge(true);
            xlWorkSheet1.get_Range("A2", "D2").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A2", "D2").Font.Color = 32768;

            xlWorkSheet1.Cells[3, 1] = "Số điện thoại: (08)38.107.373";
            xlWorkSheet1.get_Range("A3", "D3").Merge(true);
            xlWorkSheet1.get_Range("A3", "D3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A3", "D3").Font.Color = 32768;

            xlWorkSheet1.Cells[5, 4] = "Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
            xlWorkSheet1.get_Range("D5", "F5").Merge(true);
            xlWorkSheet1.get_Range("D5", "F5").HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;
            xlWorkSheet1.get_Range("D5", "F5").Font.Italic = true;


            xlWorkSheet1.Cells[6, 1] = "BÁO CÁO TÌNH HÌNH NHẬP HÀNG";
            xlWorkSheet1.get_Range("A6", "F6").Merge(true);
            xlWorkSheet1.get_Range("A6", "F6").Font.Bold = true;
            xlWorkSheet1.get_Range("A6", "F6").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A6", "F6").Font.Color = 8388608;

            if("".Equals(thang.Text) || !IsNumber(thang.Text))
                xlWorkSheet1.Cells[7, 1] = "Năm " + nam.Text;
            else
                xlWorkSheet1.Cells[7, 1] = "Tháng " + thang.Text + " năm " + nam.Text;
            xlWorkSheet1.get_Range("A7", "F7").Merge(true);
            xlWorkSheet1.get_Range("A7", "F7").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

            xlWorkSheet1.Cells[9, 2] = "Tổng số lượng nhập: " + tongsoluong.Text;
            xlWorkSheet1.get_Range("B9", "E9").Merge(true);

            xlWorkSheet1.Cells[10, 2] = "Tổng thành tiền: " + tongtien.Text;
            xlWorkSheet1.get_Range("B10", "E10").Merge(true);
            int i = 0;
            int j = 0;
            xlWorkSheet1.Cells[12, 2] = "STT";
            xlWorkSheet1.Cells[12, 3] = "Ngày";
            xlWorkSheet1.Cells[12, 4] = "Số lượng nhập (quyển)";
            xlWorkSheet1.Cells[12, 5] = "Tổng tiền (VNĐ)";
            xlWorkSheet1.get_Range("B12", "E12").Font.Bold = true;
            int x = danhsachmuahang.Rows.Count + 12;
            for (i = 0; i <= danhsachmuahang.RowCount - 1; i++)
            {
                for (j = 0; j <= danhsachmuahang.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = danhsachmuahang[j, i];
                    xlWorkSheet1.Cells[i + 13, j + 2] = cell.Value;
                    if (danhsachmuahang.Rows[i].Cells[0].Value == null)
                        break;
                }
            }

            xlWorkSheet1.get_Range("B12", "E"+ x).Borders.Color = 0;

            string a = DateTime.Now.ToString("ddmmyyhhmmss");
            xlWorkBook.SaveAs("D:\\Baocaonhaphang" + a + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet1);
            releaseObject(xlWorkSheet2);
            releaseObject(xlWorkSheet3);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Xuất thành công! Địa chỉ tập tin là D:\\Baocaonhaphang" + a + ".xls");
        }

        public void ExportToExcel_ban(DataGridView danhsachbanhang, TextBox tongsoluong, TextBox tongtien, ComboBox thang, ComboBox nam)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet1 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet2 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet3 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet1.Name = "Thống kê tình hình bán hàng";
            xlWorkSheet1.Cells[1, 1] = "CỬA HÀNG SÁCH SAIGONBOOK";
            xlWorkSheet1.get_Range("A1", "D1").Merge(true);
            xlWorkSheet1.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet1.get_Range("A1", "D1").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A1", "D1").Font.Color = 32768;

            xlWorkSheet1.Cells[2, 1] = "Địa chỉ: 273 An Dương Vương, Quận 5";
            xlWorkSheet1.get_Range("A2", "D2").Merge(true);
            xlWorkSheet1.get_Range("A2", "D2").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A2", "D2").Font.Color = 32768;

            xlWorkSheet1.Cells[3, 1] = "Số điện thoại: (08)38.107.373";
            xlWorkSheet1.get_Range("A3", "D3").Merge(true);
            xlWorkSheet1.get_Range("A3", "D3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A3", "D3").Font.Color = 32768;

            xlWorkSheet1.Cells[5, 4] = "Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
            xlWorkSheet1.get_Range("D5", "F5").Merge(true);
            xlWorkSheet1.get_Range("D5", "F5").HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;
            xlWorkSheet1.get_Range("D5", "F5").Font.Italic = true;


            xlWorkSheet1.Cells[6, 1] = "BÁO CÁO TÌNH HÌNH BÁN HÀNG";
            xlWorkSheet1.get_Range("A6", "F6").Merge(true);
            xlWorkSheet1.get_Range("A6", "F6").Font.Bold = true;
            xlWorkSheet1.get_Range("A6", "F6").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A6", "F6").Font.Color = 8388608;

            if ("".Equals(thang.Text) || !IsNumber(thang.Text))
                xlWorkSheet1.Cells[7, 1] = "Năm " + nam.Text;
            else
                xlWorkSheet1.Cells[7, 1] = "Tháng " + thang.Text + " năm " + nam.Text;
            xlWorkSheet1.get_Range("A7", "F7").Merge(true);
            xlWorkSheet1.get_Range("A7", "F7").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

            xlWorkSheet1.Cells[9, 2] = "Tổng số lượng bán: " + tongsoluong.Text;
            xlWorkSheet1.get_Range("B9", "E9").Merge(true);

            xlWorkSheet1.Cells[10, 2] = "Tổng thành tiền: " + tongtien.Text;
            xlWorkSheet1.get_Range("B10", "E10").Merge(true);
            int i = 0;
            int j = 0;
            xlWorkSheet1.Cells[12, 2] = "STT";
            xlWorkSheet1.Cells[12, 3] = "Ngày";
            xlWorkSheet1.Cells[12, 4] = "Số lượng bán (quyển)";
            xlWorkSheet1.Cells[12, 5] = "Tổng tiền (VNĐ)";
            xlWorkSheet1.get_Range("B12", "E12").Font.Bold = true;
            int x = danhsachbanhang.Rows.Count + 12;
            for (i = 0; i <= danhsachbanhang.RowCount - 1; i++)
            {
                for (j = 0; j <= danhsachbanhang.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = danhsachbanhang[j, i];
                    xlWorkSheet1.Cells[i + 13, j + 2] = cell.Value;
                    if (danhsachbanhang.Rows[i].Cells[0].Value == null)
                        break;
                }
            }

            xlWorkSheet1.get_Range("B12", "E" + x).Borders.Color = 0;
            string a = DateTime.Now.ToString("ddmmyyhhmmss");
            xlWorkBook.SaveAs("D:\\Baocaobanhang" + a + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet1);
            releaseObject(xlWorkSheet2);
            releaseObject(xlWorkSheet3);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Xuất thành công! Địa chỉ tập tin là D:\\Baocaobanhang" + a + ".xls");
        }

        public void ExportToExcel_phieunhap(TextBox txtmaphieu, TextBox txtngaynhap, TextBox txtnhacungcap, TextBox txtdiachi, 
                               TextBox txtsodienthoai, TextBox txtemail, TextBox txttongtien, DataGridView danhsachchitietphieunhap)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet1 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet2 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet3 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet1.Name = "Phiếu nhập";
            xlWorkSheet1.Cells[1, 1] = "CỬA HÀNG SÁCH SAIGONBOOK";
            xlWorkSheet1.get_Range("A1", "D1").Merge(true);
            xlWorkSheet1.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet1.get_Range("A1", "D1").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A1", "D1").Font.Color = 32768;

            xlWorkSheet1.Cells[2, 1] = "Địa chỉ: 273 An Dương Vương, Quận 5";
            xlWorkSheet1.get_Range("A2", "D2").Merge(true);
            xlWorkSheet1.get_Range("A2", "D2").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A2", "D2").Font.Color = 32768;

            xlWorkSheet1.Cells[3, 1] = "Số điện thoại: (08)38.107.373";
            xlWorkSheet1.get_Range("A3", "D3").Merge(true);
            xlWorkSheet1.get_Range("A3", "D3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A3", "D3").Font.Color = 32768;

            xlWorkSheet1.Cells[5, 3] = "Xuất phiếu: ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
            xlWorkSheet1.get_Range("C5", "F5").Merge(true);
            xlWorkSheet1.get_Range("C5", "F5").HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;
            xlWorkSheet1.get_Range("C5", "F5").Font.Italic = true;


            xlWorkSheet1.Cells[7, 1] = "PHIẾU NHẬP HÀNG";
            xlWorkSheet1.get_Range("A7", "F7").Merge(true);
            xlWorkSheet1.get_Range("A7", "F7").Font.Bold = true;
            xlWorkSheet1.get_Range("A7", "F7").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A7", "F7").Font.Color = 8388608;

            xlWorkSheet1.Cells[8, 2] = "Mã phiếu: " + txtmaphieu.Text;
            xlWorkSheet1.get_Range("B8", "C8").Merge(true);

            xlWorkSheet1.Cells[8, 4] = "Ngày nhập: " + txtngaynhap.Text;
            xlWorkSheet1.get_Range("D8", "F8").Merge(true);
            xlWorkSheet1.get_Range("D8", "F8").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

            xlWorkSheet1.Cells[9, 2] = "Nhà cung cấp: " + txtnhacungcap.Text.ToUpper();
            xlWorkSheet1.get_Range("B9", "F9").Merge(true);
            xlWorkSheet1.get_Range("B9", "F9").Font.Bold = true;

            xlWorkSheet1.Cells[10, 2] = "Địa chỉ: " + txtdiachi.Text;
            xlWorkSheet1.get_Range("B10", "F10").Merge(true);
            xlWorkSheet1.get_Range("B10", "F10").Font.Bold = true;

            xlWorkSheet1.Cells[11, 2] = "Số điện thoại: " + txtsodienthoai.Text;
            xlWorkSheet1.get_Range("B11", "F11").Merge(true);
            xlWorkSheet1.get_Range("B11", "F11").Font.Bold = true;

            xlWorkSheet1.Cells[12, 2] = "Email: " + txtemail.Text;
            xlWorkSheet1.get_Range("B12", "F12").Merge(true);
            xlWorkSheet1.get_Range("B12", "F12").Font.Bold = true;

            xlWorkSheet1.Cells[15, 2] = "Tổng thành tiền: " + txttongtien.Text;
            xlWorkSheet1.get_Range("B15", "E15").Merge(true);
            int i = 0;
            int j = 0;
            xlWorkSheet1.Cells[17, 1] = "STT";
            xlWorkSheet1.Cells[17, 2] = "Mã sách";
            xlWorkSheet1.Cells[17, 3] = "Tên sách";
            xlWorkSheet1.Cells[17, 4] = "Đơn giá (VNĐ)";
            xlWorkSheet1.Cells[17, 5] = "Số lượng";
            xlWorkSheet1.Cells[17, 6] = "Thành tiền (VNĐ)";
            xlWorkSheet1.get_Range("A17", "F17").Font.Bold = true;
            int x = danhsachchitietphieunhap.Rows.Count + 17;
            int soluongsach = 0;
            for (i = 0; i <= danhsachchitietphieunhap.RowCount - 1; i++)
            {
                soluongsach += int.Parse(danhsachchitietphieunhap.Rows[i].Cells[4].Value.ToString());
                for (j = 0; j <= danhsachchitietphieunhap.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = danhsachchitietphieunhap[j, i];
                    xlWorkSheet1.Cells[i + 18, j + 1] = cell.Value;
                    if (danhsachchitietphieunhap.Rows[i].Cells[0].Value == null)
                        break;
                }
            }
            xlWorkSheet1.Cells[14, 2] = "Tổng số lượng nhập: " + soluongsach;
            xlWorkSheet1.get_Range("B14", "E14").Merge(true);

            xlWorkSheet1.get_Range("A17", "F" + x).Borders.Color = 0;

            string a = DateTime.Now.ToString("ddmmyyhhmmss");
            xlWorkBook.SaveAs("D:\\Phieunhap" + a + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet1);
            releaseObject(xlWorkSheet2);
            releaseObject(xlWorkSheet3);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Xuất thành công! Địa chỉ tập tin là D:\\Phieunhap" + a + ".xls");
        }

        public void ExportToExcel_hoadon(TextBox txtmaphieu, TextBox txtngaydatmua, DateTimePicker ngaygiao, TextBox txttenkhachhang, TextBox txtdiachi,
                               TextBox txtsodienthoai, TextBox txttongtien, DataGridView danhsachchitietdonhang)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet1 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet2 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet3 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet1.Name = "Hóa đơn";
            xlWorkSheet1.Cells[1, 1] = "CỬA HÀNG SÁCH SAIGONBOOK";
            xlWorkSheet1.get_Range("A1", "D1").Merge(true);
            xlWorkSheet1.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet1.get_Range("A1", "D1").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A1", "D1").Font.Color = 32768;

            xlWorkSheet1.Cells[2, 1] = "Địa chỉ: 273 An Dương Vương, Quận 5";
            xlWorkSheet1.get_Range("A2", "D2").Merge(true);
            xlWorkSheet1.get_Range("A2", "D2").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A2", "D2").Font.Color = 32768;

            xlWorkSheet1.Cells[3, 1] = "Số điện thoại: (08)38.107.373";
            xlWorkSheet1.get_Range("A3", "D3").Merge(true);
            xlWorkSheet1.get_Range("A3", "D3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A3", "D3").Font.Color = 32768;

            xlWorkSheet1.Cells[5, 3] = "Xuất phiếu: ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
            xlWorkSheet1.get_Range("C5", "F5").Merge(true);
            xlWorkSheet1.get_Range("C5", "F5").HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;
            xlWorkSheet1.get_Range("C5", "F5").Font.Italic = true;


            xlWorkSheet1.Cells[7, 1] = "HÓA ĐƠN THANH TOÁN";
            xlWorkSheet1.get_Range("A7", "F7").Merge(true);
            xlWorkSheet1.get_Range("A7", "F7").Font.Bold = true;
            xlWorkSheet1.get_Range("A7", "F7").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A7", "F7").Font.Color = 8388608;

            xlWorkSheet1.Cells[8, 2] = "Mã phiếu: " + txtmaphieu.Text;
            xlWorkSheet1.get_Range("B8", "C8").Merge(true);

            xlWorkSheet1.Cells[8, 4] = "Ngày đặt hàng: " + txtngaydatmua.Text;
            xlWorkSheet1.get_Range("D8", "F8").Merge(true);
            xlWorkSheet1.get_Range("D8", "F8").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

            xlWorkSheet1.Cells[9, 2] = "Khách hàng: " + txttenkhachhang.Text.ToUpper();
            xlWorkSheet1.get_Range("B9", "F9").Merge(true);
            xlWorkSheet1.get_Range("B9", "F9").Font.Bold = true;

            xlWorkSheet1.Cells[10, 2] = "Ngày giao hàng: " + ngaygiao.Value.Date.ToString("dd-MM-yyyy");
            xlWorkSheet1.get_Range("B10", "F10").Merge(true);
            xlWorkSheet1.get_Range("B10", "F10").Font.Bold = true;

            xlWorkSheet1.Cells[11, 2] = "Địa chỉ giao hàng: " + txtdiachi.Text;
            xlWorkSheet1.get_Range("B11", "F11").Merge(true);
            xlWorkSheet1.get_Range("B11", "F11").Font.Bold = true;

            xlWorkSheet1.Cells[12, 2] = "Số điện thoại: " + txtsodienthoai.Text;
            xlWorkSheet1.get_Range("B12", "F12").Merge(true);
            xlWorkSheet1.get_Range("B12", "F12").Font.Bold = true;

            xlWorkSheet1.Cells[15, 2] = "Tổng thành tiền: " + txttongtien.Text;
            xlWorkSheet1.get_Range("B15", "E15").Merge(true);
            int i = 0;
            int j = 0;
            xlWorkSheet1.Cells[17, 1] = "STT";
            xlWorkSheet1.Cells[17, 2] = "Mã sách";
            xlWorkSheet1.Cells[17, 3] = "Tên sách";
            xlWorkSheet1.Cells[17, 4] = "Đơn giá (VNĐ)";
            xlWorkSheet1.Cells[17, 5] = "Số lượng";
            xlWorkSheet1.Cells[17, 6] = "Thành tiền (VNĐ)";
            xlWorkSheet1.get_Range("A17", "F17").Font.Bold = true;
            int x = danhsachchitietdonhang.Rows.Count + 17;
            int soluongsach = 0;
            for (i = 0; i <= danhsachchitietdonhang.RowCount - 1; i++)
            {
                soluongsach += int.Parse(danhsachchitietdonhang.Rows[i].Cells[4].Value.ToString());
                for (j = 0; j <= danhsachchitietdonhang.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = danhsachchitietdonhang[j, i];
                    xlWorkSheet1.Cells[i + 18, j + 1] = cell.Value;
                    if (danhsachchitietdonhang.Rows[i].Cells[0].Value == null)
                        break;
                }
            }
            xlWorkSheet1.Cells[14, 2] = "Tổng số lượng: " + soluongsach + " (quyển)";
            xlWorkSheet1.get_Range("B14", "E14").Merge(true);

            xlWorkSheet1.get_Range("A17", "F" + x).Borders.Color = 0;

            string a = DateTime.Now.ToString("ddmmyyhhmmss");
            xlWorkBook.SaveAs("D:\\Hoadon" + a + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet1);
            releaseObject(xlWorkSheet2);
            releaseObject(xlWorkSheet3);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Xuất thành công! Địa chỉ tập tin là D:\\Hoadon" + a + ".xls");
        }

        public void ExportToExcel_tonkho(DateTimePicker ngay, ComboBox loai, DataGridView DataGV_danhsachtonkho, TextBox txttongsoluong)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet1 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet2 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            Excel.Worksheet xlWorkSheet3 = xlWorkBook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                        as Excel.Worksheet;
            xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet1.Name = "Báo cáo tồn kho";
            xlWorkSheet1.Cells[1, 1] = "CỬA HÀNG SÁCH SAIGONBOOK";
            xlWorkSheet1.get_Range("A1", "D1").Merge(true);
            xlWorkSheet1.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet1.get_Range("A1", "D1").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A1", "D1").Font.Color = 32768;

            xlWorkSheet1.Cells[2, 1] = "Địa chỉ: 273 An Dương Vương, Quận 5";
            xlWorkSheet1.get_Range("A2", "D2").Merge(true);
            xlWorkSheet1.get_Range("A2", "D2").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A2", "D2").Font.Color = 32768;

            xlWorkSheet1.Cells[3, 1] = "Số điện thoại: (08)38.107.373";
            xlWorkSheet1.get_Range("A3", "D3").Merge(true);
            xlWorkSheet1.get_Range("A3", "D3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A3", "D3").Font.Color = 32768;

            xlWorkSheet1.Cells[5, 3] = "Xuất phiếu: ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
            xlWorkSheet1.get_Range("C5", "F5").Merge(true);
            xlWorkSheet1.get_Range("C5", "F5").HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;
            xlWorkSheet1.get_Range("C5", "F5").Font.Italic = true;


            xlWorkSheet1.Cells[7, 1] = "BÁO CÁO TỒN KHO";
            xlWorkSheet1.get_Range("A7", "F7").Merge(true);
            xlWorkSheet1.get_Range("A7", "F7").Font.Bold = true;
            xlWorkSheet1.get_Range("A7", "F7").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet1.get_Range("A7", "F7").Font.Color = 8388608;


            xlWorkSheet1.Cells[9, 2] = "Ngày thống kê: " + ngay.Value.Date.ToString("dd-MM-yyyy");
            xlWorkSheet1.get_Range("B9", "F9").Merge(true);
            xlWorkSheet1.get_Range("B9", "F9").Font.Bold = true;

            xlWorkSheet1.Cells[10, 2] = "Loại sách: " + loai.Text;
            xlWorkSheet1.get_Range("B10", "F10").Merge(true);
            xlWorkSheet1.get_Range("B10", "F10").Font.Bold = true;

            xlWorkSheet1.Cells[11, 2] = "Tổng số tồn kho: " + txttongsoluong.Text;
            xlWorkSheet1.get_Range("B11", "F11").Merge(true);
            xlWorkSheet1.get_Range("B11", "F11").Font.Bold = true;

            int i = 0;
            int j = 0;
            xlWorkSheet1.Cells[13, 1] = "STT";
            xlWorkSheet1.Cells[13, 2] = "Mã sách";
            xlWorkSheet1.Cells[13, 3] = "Tên sách";
            xlWorkSheet1.Cells[13, 4] = "Loại sách";
            xlWorkSheet1.Cells[13, 5] = "Đơn giá (VNĐ)";
            xlWorkSheet1.Cells[13, 6] = "Số lượng còn";
            xlWorkSheet1.get_Range("A13", "F13").Font.Bold = true;
            int x = DataGV_danhsachtonkho.Rows.Count + 13;
            for (i = 0; i <= DataGV_danhsachtonkho.RowCount - 1; i++)
            {
                for (j = 0; j <= DataGV_danhsachtonkho.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = DataGV_danhsachtonkho[j, i];
                    xlWorkSheet1.Cells[i + 14, j + 1] = cell.Value;
                    if (DataGV_danhsachtonkho.Rows[i].Cells[0].Value == null)
                        break;
                }
            }

            xlWorkSheet1.get_Range("A13", "F" + x).Borders.Color = 0;

            string a = DateTime.Now.ToString("ddmmyyhhmmss");
            xlWorkBook.SaveAs("D:\\Baocaotonkho" + a + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet1);
            releaseObject(xlWorkSheet2);
            releaseObject(xlWorkSheet3);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Xuất thành công! Địa chỉ tập tin là D:\\Baocaotonkho" + a + ".xls");
        }
    }
}
